"""
Case Type   : Ubtree
Case Name   : alter ubtree索引(指定PCR)_索引分区不可用后重建索引分区
Create At   : 2025-11-04
Owner       : xiongqin3
Description :
    1:建表;
    2:建索引时指定PCR
    3:插入数据
    4:设置表索引不可用
    5:重建索引分区
    6:查询
    7:查询计划
    8:清理环境
Expect      :
    1、成功
    2、成功
    3、成功
    4、成功
    5、成功
    6、成功
    7、均使用indexscan
    8、成功
History     :
"""


import os
import unittest

from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger

logger = Logger()


class UstoreUbtreeCase0064(unittest.TestCase):
    def setUp(self):
        logger.info(f'-----{os.path.basename(__file__)} start-----')
        self.constant = Constant()
        self.comsh = CommonSH('PrimaryDbUser')
        status = self.comsh.get_db_cluster_status('status')
        self.assertTrue(status)
        self.table_name = 't_ubtree_0064'
        self.prepare_data_01 = f"drop table if exists {self.table_name} cascade;" \
                               f"create table {self.table_name} (" \
                               f"sale_id int primary key," \
                               f"sale_date date not null," \
                               f"amount numeric(10,2)" \
                               f") with (storage_type=ustore)" \
                               f"partition by range (sale_date) (" \
                               f"partition p_202501 values less than ('2025-02-01')," \
                               f"partition p_202502 values less than ('2025-03-01')," \
                               f"partition p_202503 values less than (maxvalue));"
        self.prepare_data_02 = f"create index i_ubtree_0064 on {self.table_name} using ubtree(sale_date) local with (index_type='pcr');" \
                               f"select relkind, reloptions from pg_class where relname like 'i_ubtree_0064%';" \
                               f"select relname, parttype, indisusable from pg_partition where parentid = " \
                               f"(select oid from pg_class where relname = 'i_ubtree_0064');"
        self.prepare_data_03 = f"insert into {self.table_name} values (1, '2025-03-01', 150.50)," \
                               f"(2, '2025-12-15', 200.00), (3, '2025-01-15', 300.00), (4, '2026-05-20', 500.00);"
        self.prepare_data_04 = f"alter index if exists i_ubtree_0064 modify partition p_202501_sale_date_idx unusable;" \
                               f"select relname, parttype, indisusable from pg_partition where parentid = " \
                               f"(select oid from pg_class where relname = 'i_ubtree_0064');"
        self.prepare_data_05 = f"alter index i_ubtree_0064 rebuild partition p_202501_sale_date_idx;" \
                               f"select relname, parttype, indisusable from pg_partition where parentid = " \
                               f"(select oid from pg_class where relname = 'i_ubtree_0064');"
        self.prepare_data_06 = f"select * from {self.table_name} where sale_date = '2025-03-01';" \
                               f"select * from {self.table_name} where sale_date = '2025-01-15';"
        self.prepare_data_07 = f"set enable_seqscan = off;" \
                               f"explain (costs off) select * from {self.table_name} where sale_date = '2025-03-01';" \
                               f"explain (costs off) select * from {self.table_name} where sale_date = '2025-01-15';"
        self.prepare_data_08 = f"drop table if exists {self.table_name} cascade;" \
                               f"set enable_seqscan = on;"
        
    def test_slowsql(self):
        text = '--step1:建表;expect:成功--'
        logger.info(text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_01)
        logger.info(data_cmd)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, data_cmd, '执行失败' + text)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, data_cmd, '执行失败' + text)

        text = "--step2:建索引时指定PCR;expect:成功--"
        logger.info(text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_02)
        logger.info(data_cmd)
        self.assertIn(self.constant.CREATE_INDEX_SUCCESS_MSG, data_cmd, '执行失败' + text)
        self.assertIn("i       | {index_type=pcr,storage_type=ustore}", data_cmd, '执行失败' + text)
        self.assertIn("p_202501_sale_date_idx | x        | t", data_cmd, '执行失败' + text)
        self.assertIn("p_202502_sale_date_idx | x        | t", data_cmd, '执行失败' + text)
        self.assertIn("p_202503_sale_date_idx | x        | t", data_cmd, '执行失败' + text)

        text = '--step3:插入数据;expect:成功--'
        logger.info(text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_03)
        logger.info(data_cmd)
        self.assertIn(self.constant.INSERT_SUCCESS_MSG, data_cmd, '执行失败' + text)

        text = '--step4:设置表索引不可用;expect:成功--'
        logger.info(text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_04)
        logger.info(data_cmd)
        self.assertIn(self.constant.ALTER_INDEX_SUCCESS_MSG, data_cmd, '执行失败' + text)
        self.assertIn("p_202501_sale_date_idx | x        | f", data_cmd, '执行失败' + text)
        self.assertIn("p_202502_sale_date_idx | x        | t", data_cmd, '执行失败' + text)
        self.assertIn("p_202503_sale_date_idx | x        | t", data_cmd, '执行失败' + text)

        text = '--step5:重建索引分区;expect:走indexscan--'
        logger.info(text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_05)
        logger.info(data_cmd)
        self.assertIn(self.constant.REINDEX_SUCCESS_MSG, data_cmd, '执行失败' + text)
        self.assertIn("p_202501_sale_date_idx | x        | t", data_cmd, '执行失败' + text)
        self.assertIn("p_202502_sale_date_idx | x        | t", data_cmd, '执行失败' + text)
        self.assertIn("p_202503_sale_date_idx | x        | t", data_cmd, '执行失败' + text)

        text = '--step6:查询;expect:成功--'
        logger.info(text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_06)
        logger.info(data_cmd)
        self.assertIn("1 | 2025-03-01 00:00:00 | 150.50", data_cmd, '执行失败' + text)
        self.assertIn("3 | 2025-01-15 00:00:00 | 300.00", data_cmd, '执行失败' + text)

        text = '--step7:查询计划;expect:均使用indexscan--'
        logger.info(text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_07)
        logger.info(data_cmd)
        self.assertIn("Index Cond: (sale_date = '2025-03-01 00:00:00'::timestamp without time zone)", data_cmd, '执行失败' + text)
        self.assertIn("Index Cond: (sale_date = '2025-01-15 00:00:00'::timestamp without time zone)", data_cmd, '执行失败' + text)

    def tearDown(self):
        text = '--step8:清理环境;expect:成功--'
        logger.info(text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_08)
        logger.info(data_cmd)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, data_cmd, '执行失败' + text)
        logger.info(f'-----{os.path.basename(__file__)} end-----')

